<!DOCTYPE html>
<html lang=zh>
<head>
  <meta charset="utf-8">
  
  <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, minimum-scale=1, user-scalable=no, minimal-ui">
  <meta name="renderer" content="webkit">
  <meta http-equiv="Cache-Control" content="no-transform" />
  <meta http-equiv="Cache-Control" content="no-siteapp" />
  <meta name="apple-mobile-web-app-capable" content="yes">
  <meta name="apple-mobile-web-app-status-bar-style" content="black">
  <meta name="format-detection" content="telephone=no,email=no,adress=no">
  <!-- Color theme for statusbar -->
  <meta name="theme-color" content="#000000" />
  <!-- 强制页面在当前窗口以独立页面显示,防止别人在框架里调用页面 -->
  <meta http-equiv="window-target" content="_top" />
  
  
  <title>mysql基础 | 逸辰</title>
  <meta name="description" content="1、Mysql1.1连接数据库命令行连接 12mysql -uroot -pchen --连接数据库show databases;  -- 查看所有数据库   1use demo; -- 切换数据库demo   1show tables; -- 查看数据库中所有表   1describe book; -- 查看表的详细信息   12create database test; -- 创建一个test">
<meta property="og:type" content="article">
<meta property="og:title" content="mysql基础">
<meta property="og:url" content="https://yichenfirst.github.io/2021/08/31/mysql/mysql/index.html">
<meta property="og:site_name" content="逸辰">
<meta property="og:description" content="1、Mysql1.1连接数据库命令行连接 12mysql -uroot -pchen --连接数据库show databases;  -- 查看所有数据库   1use demo; -- 切换数据库demo   1show tables; -- 查看数据库中所有表   1describe book; -- 查看表的详细信息   12create database test; -- 创建一个test">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://yichen-blog.oss-cn-beijing.aliyuncs.com//note-image/2023/07/16/20230716-162011.png">
<meta property="og:image" content="https://yichen-blog.oss-cn-beijing.aliyuncs.com//note-image/2023/07/16/20230716-162013.png">
<meta property="og:image" content="https://yichen-blog.oss-cn-beijing.aliyuncs.com//note-image/2023/07/16/20230716-162015.png">
<meta property="og:image" content="https://yichen-blog.oss-cn-beijing.aliyuncs.com//note-image/2023/07/16/20230716-162017.png">
<meta property="og:image" content="https://yichen-blog.oss-cn-beijing.aliyuncs.com//note-image/2023/07/16/20230716-162049.png">
<meta property="og:image" content="https://yichen-blog.oss-cn-beijing.aliyuncs.com//note-image/2023/07/16/20230716-162051.png">
<meta property="article:published_time" content="2021-08-30T16:00:00.000Z">
<meta property="article:modified_time" content="2023-07-17T13:41:27.804Z">
<meta property="article:author" content="逸辰">
<meta property="article:tag" content="mysql">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://yichen-blog.oss-cn-beijing.aliyuncs.com//note-image/2023/07/16/20230716-162011.png">
  <!-- Canonical links -->
  <link rel="canonical" href="https://yichenfirst.github.io/2021/08/31/mysql/mysql/index.html">
  
    <link rel="alternate" href="/atom.xml" title="逸辰" type="application/atom+xml">
  
  
    <link rel="icon" href="/favicon.png" type="image/x-icon">
  
  
<link rel="stylesheet" href="/css/style.css">

  
  
  
  
<meta name="generator" content="Hexo 6.3.0">
<style>.github-emoji { position: relative; display: inline-block; width: 1.2em; min-height: 1.2em; overflow: hidden; vertical-align: top; color: transparent; }  .github-emoji > span { position: relative; z-index: 10; }  .github-emoji img, .github-emoji .fancybox { margin: 0 !important; padding: 0 !important; border: none !important; outline: none !important; text-decoration: none !important; user-select: none !important; cursor: auto !important; }  .github-emoji img { height: 1.2em !important; width: 1.2em !important; position: absolute !important; left: 50% !important; top: 50% !important; transform: translate(-50%, -50%) !important; user-select: none !important; cursor: auto !important; } .github-emoji-fallback { color: inherit; } .github-emoji-fallback img { opacity: 0 !important; }</style>
</head>


<body class="main-center" itemscope itemtype="http://schema.org/WebPage">
  <header class="header" itemscope itemtype="http://schema.org/WPHeader">
  <div class="slimContent">
    <div class="navbar-header">
      
      
      <div class="profile-block text-center">
        <a id="avatar" href="" target="_blank">
          <img class="img-circle img-rotate" src="/images/avatar.jpg" width="200" height="200">
        </a>
        <h2 id="name" class="hidden-xs hidden-sm">逸辰</h2>
        <h3 id="title" class="hidden-xs hidden-sm hidden-md">后端开发</h3>
        <small id="location" class="text-muted hidden-xs hidden-sm"><i class="icon icon-map-marker"></i> 长春，中国</small>
      </div>
      
      <div class="search" id="search-form-wrap">

    <form class="search-form sidebar-form">
        <div class="input-group">
            <input type="text" class="search-form-input form-control" placeholder="搜索" />
            <span class="input-group-btn">
                <button type="submit" class="search-form-submit btn btn-flat" onclick="return false;"><i class="icon icon-search"></i></button>
            </span>
        </div>
    </form>
    <div class="ins-search">
  <div class="ins-search-mask"></div>
  <div class="ins-search-container">
    <div class="ins-input-wrapper">
      <input type="text" class="ins-search-input" placeholder="想要查找什么..." x-webkit-speech />
      <button type="button" class="close ins-close ins-selectable" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
    </div>
    <div class="ins-section-wrapper">
      <div class="ins-section-container"></div>
    </div>
  </div>
</div>


</div>
      <button class="navbar-toggle collapsed" type="button" data-toggle="collapse" data-target="#main-navbar" aria-controls="main-navbar" aria-expanded="false">
        <span class="sr-only">Toggle navigation</span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
      </button>
    </div>
    <nav id="main-navbar" class="collapse navbar-collapse" itemscope itemtype="http://schema.org/SiteNavigationElement" role="navigation">
      <ul class="nav navbar-nav main-nav ">
        
        
        <li class="menu-item menu-item-home">
          <a href="/.">
            
            <i class="icon icon-home-fill"></i>
            
            <span class="menu-title">首页</span>
          </a>
        </li>
        
        
        <li class="menu-item menu-item-archives">
          <a href="/archives">
            
            <i class="icon icon-archives-fill"></i>
            
            <span class="menu-title">归档</span>
          </a>
        </li>
        
        
        <li class="menu-item menu-item-categories">
          <a href="/categories">
            
            <i class="icon icon-folder"></i>
            
            <span class="menu-title">分类</span>
          </a>
        </li>
        
        
        <li class="menu-item menu-item-tags">
          <a href="/tags">
            
            <i class="icon icon-tags"></i>
            
            <span class="menu-title">标签</span>
          </a>
        </li>
        
        
        <li class="menu-item menu-item-about">
          <a href="/about">
            
            <i class="icon icon-cup-fill"></i>
            
            <span class="menu-title">关于</span>
          </a>
        </li>
        
      </ul>
      
    </nav>
  </div>
</header>

  
    <aside class="sidebar" itemscope itemtype="http://schema.org/WPSideBar">
  <div class="slimContent">
    
      <div class="widget">
    <h3 class="widget-title">公告</h3>
    <div class="widget-body">
        <div id="board">
            <div class="content">
                <p>欢迎交流与分享经验!</p>
            </div>
        </div>
    </div>
</div>

    
      
  <div class="widget">
    <h3 class="widget-title">分类</h3>
    <div class="widget-body">
      <ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="/categories/ElasticSearch/">ElasticSearch</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/docker/">docker</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/go/">go</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/java/">java</a><span class="category-list-count">6</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/mybatis/">mybatis</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/mysql/">mysql</a><span class="category-list-count">3</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/redis/">redis</a><span class="category-list-count">6</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/spring/">spring</a><span class="category-list-count">9</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/vue/">vue</a><span class="category-list-count">3</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/%E5%88%86%E5%B8%83%E5%BC%8F/">分布式</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F/">操作系统</a><span class="category-list-count">3</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%BD%91%E7%BB%9C/">计算机网络</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/%E9%9D%A2%E8%AF%95/">面试</a><span class="category-list-count">3</span></li></ul>
    </div>
  </div>


    
      
  <div class="widget">
    <h3 class="widget-title">标签</h3>
    <div class="widget-body">
      <ul class="tag-list" itemprop="keywords"><li class="tag-list-item"><a class="tag-list-link" href="/tags/ElasticSearch/" rel="tag">ElasticSearch</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/aop/" rel="tag">aop</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/docker/" rel="tag">docker</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/go/" rel="tag">go</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/java/" rel="tag">java</a><span class="tag-list-count">7</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/javascript/" rel="tag">javascript</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/mybatis/" rel="tag">mybatis</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/mysql/" rel="tag">mysql</a><span class="tag-list-count">5</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/redis/" rel="tag">redis</a><span class="tag-list-count">6</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/spring/" rel="tag">spring</a><span class="tag-list-count">9</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/springcloud/" rel="tag">springcloud</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/springsecurity/" rel="tag">springsecurity</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/tcp/" rel="tag">tcp</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/token/" rel="tag">token</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/vue/" rel="tag">vue</a><span class="tag-list-count">4</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/%E6%80%BB%E7%BB%93/" rel="tag">总结</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F/" rel="tag">操作系统</a><span class="tag-list-count">3</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/%E9%9D%A2%E8%AF%95/" rel="tag">面试</a><span class="tag-list-count">1</span></li></ul>
    </div>
  </div>


    
      
  <div class="widget">
    <h3 class="widget-title">标签云</h3>
    <div class="widget-body tagcloud">
      <a href="/tags/ElasticSearch/" style="font-size: 13px;">ElasticSearch</a> <a href="/tags/aop/" style="font-size: 13px;">aop</a> <a href="/tags/docker/" style="font-size: 13px;">docker</a> <a href="/tags/go/" style="font-size: 13px;">go</a> <a href="/tags/java/" style="font-size: 13.86px;">java</a> <a href="/tags/javascript/" style="font-size: 13px;">javascript</a> <a href="/tags/mybatis/" style="font-size: 13px;">mybatis</a> <a href="/tags/mysql/" style="font-size: 13.57px;">mysql</a> <a href="/tags/redis/" style="font-size: 13.71px;">redis</a> <a href="/tags/spring/" style="font-size: 14px;">spring</a> <a href="/tags/springcloud/" style="font-size: 13px;">springcloud</a> <a href="/tags/springsecurity/" style="font-size: 13px;">springsecurity</a> <a href="/tags/tcp/" style="font-size: 13px;">tcp</a> <a href="/tags/token/" style="font-size: 13px;">token</a> <a href="/tags/vue/" style="font-size: 13.43px;">vue</a> <a href="/tags/%E6%80%BB%E7%BB%93/" style="font-size: 13.14px;">总结</a> <a href="/tags/%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F/" style="font-size: 13.29px;">操作系统</a> <a href="/tags/%E9%9D%A2%E8%AF%95/" style="font-size: 13px;">面试</a>
    </div>
  </div>

    
      
  <div class="widget">
    <h3 class="widget-title">归档</h3>
    <div class="widget-body">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/archives/2023/09/">九月 2023</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2023/07/">七月 2023</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2023/05/">五月 2023</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2023/04/">四月 2023</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2023/03/">三月 2023</a><span class="archive-list-count">5</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2023/02/">二月 2023</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2023/01/">一月 2023</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2022/12/">十二月 2022</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2022/11/">十一月 2022</a><span class="archive-list-count">4</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2022/10/">十月 2022</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2022/09/">九月 2022</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2022/08/">八月 2022</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2022/06/">六月 2022</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2022/04/">四月 2022</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2022/02/">二月 2022</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2022/01/">一月 2022</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2021/10/">十月 2021</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2021/09/">九月 2021</a><span class="archive-list-count">3</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2021/08/">八月 2021</a><span class="archive-list-count">3</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2021/07/">七月 2021</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2021/06/">六月 2021</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2021/03/">三月 2021</a><span class="archive-list-count">2</span></li></ul>
    </div>
  </div>


    
      
  <div class="widget">
    <h3 class="widget-title">最新文章</h3>
    <div class="widget-body">
      <ul class="recent-post-list list-unstyled no-thumbnail">
        
          <li>
            
            <div class="item-inner">
              <p class="item-category">
                <a class="category-link" href="/categories/%E5%88%86%E5%B8%83%E5%BC%8F/">分布式</a>
              </p>
              <p class="item-title">
                <a href="/2023/09/01/%E5%88%86%E5%B8%83%E5%BC%8F/%E5%88%86%E5%B8%83%E5%BC%8F%E4%BA%8B%E5%8A%A1/" class="title">分布式事务</a>
              </p>
              <p class="item-date">
                <time datetime="2023-09-01T08:53:49.259Z" itemprop="datePublished">2023-09-01</time>
              </p>
            </div>
          </li>
          
          <li>
            
            <div class="item-inner">
              <p class="item-category">
                <a class="category-link" href="/categories/java/">java</a>
              </p>
              <p class="item-title">
                <a href="/2023/07/28/java/Java%E7%BA%BF%E7%A8%8B%E6%A8%A1%E5%9E%8B/" class="title">Java线程模型</a>
              </p>
              <p class="item-date">
                <time datetime="2023-07-28T13:59:18.661Z" itemprop="datePublished">2023-07-28</time>
              </p>
            </div>
          </li>
          
          <li>
            
            <div class="item-inner">
              <p class="item-category">
                <a class="category-link" href="/categories/java/">java</a>
              </p>
              <p class="item-title">
                <a href="/2023/05/19/java/AQS/" class="title">AQS详解</a>
              </p>
              <p class="item-date">
                <time datetime="2023-05-18T16:00:00.000Z" itemprop="datePublished">2023-05-19</time>
              </p>
            </div>
          </li>
          
          <li>
            
            <div class="item-inner">
              <p class="item-category">
                <a class="category-link" href="/categories/go/">go</a>
              </p>
              <p class="item-title">
                <a href="/2023/05/03/go/new%E4%B8%8Emake%E5%85%B3%E9%94%AE%E5%AD%97%E7%9A%84%E5%8C%BA%E5%88%AB/" class="title">new与make关键字的区别</a>
              </p>
              <p class="item-date">
                <time datetime="2023-05-02T16:00:00.000Z" itemprop="datePublished">2023-05-03</time>
              </p>
            </div>
          </li>
          
          <li>
            
            <div class="item-inner">
              <p class="item-category">
                <a class="category-link" href="/categories/java/">java</a>
              </p>
              <p class="item-title">
                <a href="/2023/04/15/java/CAS/" class="title">CAS</a>
              </p>
              <p class="item-date">
                <time datetime="2023-04-14T16:00:00.000Z" itemprop="datePublished">2023-04-15</time>
              </p>
            </div>
          </li>
          
      </ul>
    </div>
  </div>
  

    
  </div>
</aside>

  
  
<main class="main" role="main">
  <div class="content">
  <article id="post-mysql/mysql" class="article article-type-post" itemscope itemtype="http://schema.org/BlogPosting">
    
    <div class="article-header">
      
        
  
    <h1 class="article-title" itemprop="name">
      mysql基础
    </h1>
  

      
      <div class="article-meta">
        <span class="article-date">
    <i class="icon icon-calendar-check"></i>
	<a href="/2021/08/31/mysql/mysql/" class="article-date">
	  <time datetime="2021-08-30T16:00:00.000Z" itemprop="datePublished">2021-08-31</time>
	</a>
</span>
        
  <span class="article-category">
    <i class="icon icon-folder"></i>
    <a class="article-category-link" href="/categories/mysql/">mysql</a>
  </span>

        
  <span class="article-tag">
    <i class="icon icon-tags"></i>
	<a class="article-tag-link-link" href="/tags/mysql/" rel="tag">mysql</a>
  </span>


        
	<span class="article-read hidden-xs">
	    <i class="icon icon-eye-fill" aria-hidden="true"></i>
	    <span id="busuanzi_container_page_pv">
			<span id="busuanzi_value_page_pv">0</span>
		</span>
	</span>


        <span class="post-comment"><i class="icon icon-comment"></i> <a href="/2021/08/31/mysql/mysql/#comments" class="article-comment-link">评论</a></span>
        
	
		<span class="post-wordcount hidden-xs" itemprop="wordCount">字数统计: 6.4k(字)</span>
	
	
		<span class="post-readcount hidden-xs" itemprop="timeRequired">阅读时长: 27(分)</span>
	

      </div>
    </div>
    <div class="article-entry marked-body" itemprop="articleBody">
      
        <h2 id="1、Mysql"><a href="#1、Mysql" class="headerlink" title="1、Mysql"></a>1、Mysql</h2><h3 id="1-1连接数据库"><a href="#1-1连接数据库" class="headerlink" title="1.1连接数据库"></a>1.1连接数据库</h3><p>命令行连接</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysql <span class="operator">-</span>uroot <span class="operator">-</span>pchen <span class="comment">--连接数据库</span></span><br><span class="line"><span class="keyword">show</span> databases;  <span class="comment">-- 查看所有数据库</span></span><br></pre></td></tr></tbody></table></figure>

<p><img src="https://yichen-blog.oss-cn-beijing.aliyuncs.com//note-image/2023/07/16/20230716-162011.png" alt="image-20210525093901461"></p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">use demo; <span class="comment">-- 切换数据库demo</span></span><br></pre></td></tr></tbody></table></figure>

<p><img src="https://yichen-blog.oss-cn-beijing.aliyuncs.com//note-image/2023/07/16/20230716-162013.png" alt="image-20210525094135945"></p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> tables; <span class="comment">-- 查看数据库中所有表</span></span><br></pre></td></tr></tbody></table></figure>

<p><img src="https://yichen-blog.oss-cn-beijing.aliyuncs.com//note-image/2023/07/16/20230716-162015.png" alt="image-20210525094257665"></p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">describe</span> book; <span class="comment">-- 查看表的详细信息</span></span><br></pre></td></tr></tbody></table></figure>

<p><img src="https://yichen-blog.oss-cn-beijing.aliyuncs.com//note-image/2023/07/16/20230716-162017.png" alt="image-20210525094411715"></p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> database test; <span class="comment">-- 创建一个test数据库</span></span><br><span class="line">exit; <span class="comment">--退出连接</span></span><br></pre></td></tr></tbody></table></figure>



<h2 id="2、操作数据库"><a href="#2、操作数据库" class="headerlink" title="2、操作数据库"></a>2、操作数据库</h2><h3 id="2-1-、操作数据库"><a href="#2-1-、操作数据库" class="headerlink" title="2.1 、操作数据库"></a>2.1 、操作数据库</h3><p>1.创建数据库</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> database if <span class="keyword">not</span> <span class="keyword">exists</span> test; <span class="comment">-- 如果不存在，则创建</span></span><br></pre></td></tr></tbody></table></figure>

<p>2.删除数据库</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> database if <span class="keyword">exists</span> test; <span class="comment">-- 如果存在，删除数据库</span></span><br></pre></td></tr></tbody></table></figure>

<p>3.使用数据库</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 如果表名或字段名是一个关键字，可以使用``</span></span><br><span class="line">use `<span class="keyword">user</span>`;</span><br></pre></td></tr></tbody></table></figure>

<p>4.查看数据库</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> batabases</span><br></pre></td></tr></tbody></table></figure>



<h3 id="2-2、数据库的列类型"><a href="#2-2、数据库的列类型" class="headerlink" title="2.2、数据库的列类型"></a>2.2、数据库的列类型</h3><blockquote>
<p>数值</p>
</blockquote>
<ul>
<li><p>tinyint	             十分小的数据	       1个字节</p>
</li>
<li><p>smallint             较小的数据                2个字节</p>
</li>
<li><p>mediumint       中等大小的数据         3个字节</p>
</li>
<li><p><strong>int                      标准的整数                 4个字节</strong></p>
</li>
<li><p>big                     较大的数据                 8个字节</p>
</li>
<li><p>float                   浮点数                        4个字节</p>
</li>
<li><p>double               浮点数                        8个字节</p>
</li>
<li><p>decimal              字符串形式浮点数     金融计算，一般使用</p>
</li>
</ul>
<blockquote>
<p>字符串</p>
</blockquote>
<ul>
<li>char           字符串固定大小         0~255</li>
<li><strong>varchar     可变字符串                 0~65535</strong></li>
<li>tinytext      微型文本                     2^8 -1</li>
<li><strong>text            文本串                         2^16 - 1</strong></li>
</ul>
<blockquote>
<p>时间和日期</p>
</blockquote>
<p>java.util.Date</p>
<ul>
<li>date                 YYYY-MM-DD                                    日期格式</li>
<li>time                 HH：mm：ss                                  时间格式</li>
<li><strong>datetime        YYYY-MM-DD HH：mm：ss</strong></li>
<li>timestamp      时间戳                                               1970.1.1到现在毫秒数</li>
<li>year                 年份表示</li>
</ul>
<blockquote>
<p> null</p>
</blockquote>
<ul>
<li>没有值，未知</li>
</ul>
<h3 id="2-3-、数据库字段属性"><a href="#2-3-、数据库字段属性" class="headerlink" title="2.3 、数据库字段属性"></a>2.3 、数据库字段属性</h3><p>==unsigned：==</p>
<ul>
<li>无符号整数</li>
<li>声明该列不能为复数</li>
</ul>
<p>==zerofill：==</p>
<ul>
<li>0填充</li>
<li>不足的位数，使用0来填充</li>
</ul>
<p>==自增：==</p>
<ul>
<li>通常用来设置唯一的主键，必须为整数类型</li>
</ul>
<p>==非空：==</p>
<ul>
<li>Null </li>
<li>not Null 不赋值，报错</li>
</ul>
<p>==默认：==</p>
<ul>
<li>设置默认值</li>
</ul>
<h3 id="2-4、创建数据库"><a href="#2-4、创建数据库" class="headerlink" title="2.4、创建数据库"></a>2.4、创建数据库</h3><figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 目标 : 创建一个school数据库</span></span><br><span class="line"><span class="comment">-- 创建学生表(列,字段)</span></span><br><span class="line"><span class="comment">-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email</span></span><br><span class="line"><span class="comment">-- 创建表之前 , 一定要先选择数据库</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> IF <span class="keyword">NOT</span> <span class="keyword">EXISTS</span> `student` (</span><br><span class="line">`id` <span class="type">int</span>(<span class="number">4</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span> AUTO_INCREMENT COMMENT <span class="string">'学号'</span>,</span><br><span class="line">`name` <span class="type">varchar</span>(<span class="number">30</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span> <span class="keyword">DEFAULT</span> <span class="string">'匿名'</span> COMMENT <span class="string">'姓名'</span>,</span><br><span class="line">`pwd` <span class="type">varchar</span>(<span class="number">20</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span> <span class="keyword">DEFAULT</span> <span class="string">'123456'</span> COMMENT <span class="string">'密码'</span>,</span><br><span class="line">`sex` <span class="type">varchar</span>(<span class="number">2</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span> <span class="keyword">DEFAULT</span> <span class="string">'男'</span> COMMENT <span class="string">'性别'</span>,</span><br><span class="line">`birthday` datetime <span class="keyword">DEFAULT</span> <span class="keyword">NULL</span> COMMENT <span class="string">'生日'</span>,</span><br><span class="line">`address` <span class="type">varchar</span>(<span class="number">100</span>) <span class="keyword">DEFAULT</span> <span class="keyword">NULL</span> COMMENT <span class="string">'地址'</span>,</span><br><span class="line">`email` <span class="type">varchar</span>(<span class="number">50</span>) <span class="keyword">DEFAULT</span> <span class="keyword">NULL</span> COMMENT <span class="string">'邮箱'</span>,</span><br><span class="line"><span class="keyword">PRIMARY</span> KEY (`id`)</span><br><span class="line">) ENGINE<span class="operator">=</span>InnoDB <span class="keyword">DEFAULT</span> CHARSET<span class="operator">=</span>utf8</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查看数据库的定义</span></span><br><span class="line"><span class="keyword">SHOW</span> <span class="keyword">CREATE</span> DATABASE school;</span><br><span class="line"><span class="comment">-- 查看数据表的定义</span></span><br><span class="line"><span class="keyword">SHOW</span> <span class="keyword">CREATE</span> <span class="keyword">TABLE</span> student;</span><br><span class="line"><span class="comment">-- 显示表结构</span></span><br><span class="line"><span class="keyword">DESC</span> student;  <span class="comment">-- 设置严格检查模式(不能容错了)SET sql_mode='STRICT_TRANS_TABLES';</span></span><br><span class="line"></span><br></pre></td></tr></tbody></table></figure>

<p>格式</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> [IF <span class="keyword">NOT</span> <span class="keyword">EXISTS</span>] `表名`(</span><br><span class="line">    `字段名`[属性][索引][注释],</span><br><span class="line">    `字段名`[属性][索引][注释],</span><br><span class="line">    ·······</span><br><span class="line">    `字段名`[属性][索引][注释],</span><br><span class="line">    `字段名`[属性][索引][注释],</span><br><span class="line">    `字段名`[属性][索引][注释],</span><br><span class="line">)[表类型][字符集设置][注释]</span><br></pre></td></tr></tbody></table></figure>



<h3 id="2-5-、数据表的类型"><a href="#2-5-、数据表的类型" class="headerlink" title="2.5 、数据表的类型"></a>2.5 、数据表的类型</h3><figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 数据库引擎</span></span><br><span class="line"></span><br><span class="line">INNODB  默认使用</span><br><span class="line">MYISAM  早年使用</span><br></pre></td></tr></tbody></table></figure>



<table>
<thead>
<tr>
<th></th>
<th>INNODB</th>
<th>MYISAM</th>
</tr>
</thead>
<tbody><tr>
<td>事物支持</td>
<td>支持</td>
<td>不支持</td>
</tr>
<tr>
<td>数据行锁定</td>
<td>支持</td>
<td>不支持</td>
</tr>
<tr>
<td>外键约束</td>
<td>支持</td>
<td>不支持</td>
</tr>
<tr>
<td>全文索引</td>
<td>不支持</td>
<td>支持</td>
</tr>
<tr>
<td>表空间大小</td>
<td>较大，约2倍</td>
<td>较小</td>
</tr>
</tbody></table>
<p>常规操作：</p>
<ul>
<li>MYISAM 节约空间 速度较快</li>
<li>INNODB 安全性高，事物处理， 多表多用户操作</li>
</ul>
<blockquote>
<p>在物理空间存在的位置</p>
</blockquote>
<p>所有数据库文件都存在data目录文件下</p>
<p>本质是文件存储</p>
<p>MySQL引擎在物理文件上的区别</p>
<ul>
<li>INNODB在数据库表只有一个*.frm文件，以及上级目录下的ibdata1文件</li>
<li>MYISAM对应文件<ul>
<li>*.frm       表结构的定义文件</li>
<li>*.MYD     数据文件（data）</li>
<li>*.MYI       索引文件（index）</li>
</ul>
</li>
</ul>
<blockquote>
<p>设数据库表的字符集编码</p>
</blockquote>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">CHARSET<span class="operator">=</span>utf8</span><br></pre></td></tr></tbody></table></figure>

<p>mysql默认编码不支持中文</p>
<p>或在my.ini中配置默认编码</p>
<figure class="highlight ini"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="attr">character-set-server</span>=utf8</span><br></pre></td></tr></tbody></table></figure>



<h3 id="2-6-、修改和删除"><a href="#2-6-、修改和删除" class="headerlink" title="2.6 、修改和删除"></a>2.6 、修改和删除</h3><p>1.修改表</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 修改表明 alter table 旧表名 rename as 新表名</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> student rename <span class="keyword">as</span> student1</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 增加表字段 alter table 表名 add 字段名 列属性</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> student1 <span class="keyword">add</span> age <span class="type">int</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 修改表的字段</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> student1 modify age <span class="type">varchar</span>(<span class="number">11</span>)   <span class="comment">-- 修改约束</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> student1 change age age1 <span class="type">int</span>      <span class="comment">-- 字段重命名</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 删除表字段</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> student1 <span class="keyword">drop</span> age1</span><br></pre></td></tr></tbody></table></figure>

<p>2.删除表</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 删除表（如果表存在再删除）</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> if <span class="keyword">exists</span> student1</span><br></pre></td></tr></tbody></table></figure>





<h2 id="3、MySQL的数据管理"><a href="#3、MySQL的数据管理" class="headerlink" title="3、MySQL的数据管理"></a>3、MySQL的数据管理</h2><h3 id="3-1、外键"><a href="#3-1、外键" class="headerlink" title="3.1、外键"></a>3.1、外键</h3><ul>
<li>不推荐使用</li>
<li>数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）</li>
<li>可以通多程序实现外键</li>
</ul>
<h3 id="3-2、DML语言"><a href="#3-2、DML语言" class="headerlink" title="3.2、DML语言"></a>3.2、DML语言</h3><p>数据库意义：数据存储、数据管理</p>
<p>DML语言：数据操作语言</p>
<ul>
<li>insert </li>
<li>update</li>
<li>delete</li>
</ul>
<h3 id="3-3、添加"><a href="#3-3、添加" class="headerlink" title="3.3、添加"></a>3.3、添加</h3><figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 插入语句</span></span><br><span class="line"><span class="comment">-- insert into 表名([字段1， 字段2， 字段3]) values('值1','值2','值3')</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student1(`name`) <span class="keyword">values</span>(<span class="string">'张三'</span>)</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 插入多个字段</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student1(`name`) <span class="keyword">values</span>(<span class="string">'李四'</span>),(<span class="string">'王五'</span>)</span><br></pre></td></tr></tbody></table></figure>

<p>注意事项：</p>
<ul>
<li>字段可以省略，但是字段和值要一一对应</li>
<li>可以同时插入多条数据，values后面用‘，’隔开，即values( ),( )</li>
</ul>
<h3 id="3-4、修改"><a href="#3-4、修改" class="headerlink" title="3.4、修改"></a>3.4、修改</h3><figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 修改</span></span><br><span class="line"><span class="keyword">update</span> `student1` <span class="keyword">set</span> name<span class="operator">=</span><span class="string">'chen'</span> <span class="keyword">where</span> id <span class="operator">=</span> <span class="number">1</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 不加条件，修改所有</span></span><br><span class="line"><span class="keyword">update</span> `stuent1` <span class="keyword">set</span> name <span class="operator">=</span> <span class="string">'chen'</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 语法</span></span><br><span class="line"><span class="comment">-- update 表名 set 字段名1=值2,[字段名1=值2......] where [条件]</span></span><br></pre></td></tr></tbody></table></figure>

<p>条件：where字句，运算符，id等于某个值，大于某个值，在某个区间内修改·······</p>
<ul>
<li>where 后面的条件为True时，对全部数据进行修改</li>
</ul>
<table>
<thead>
<tr>
<th>操作符</th>
<th>含义</th>
<th>范围</th>
<th>结果</th>
</tr>
</thead>
<tbody><tr>
<td>=</td>
<td>等于</td>
<td>5 = 6</td>
<td>false</td>
</tr>
<tr>
<td>&lt;&gt;或!=</td>
<td>不等于</td>
<td>5 &lt;&gt; 6</td>
<td>true</td>
</tr>
<tr>
<td>&gt;</td>
<td>大于</td>
<td></td>
<td></td>
</tr>
<tr>
<td>&lt;</td>
<td>小于</td>
<td></td>
<td></td>
</tr>
<tr>
<td>&gt;=</td>
<td>大于等于</td>
<td></td>
<td></td>
</tr>
<tr>
<td>&lt;=</td>
<td>小于等于</td>
<td></td>
<td></td>
</tr>
<tr>
<td>between … and …</td>
<td>在某个范围</td>
<td>[2, 5]</td>
<td></td>
</tr>
<tr>
<td>and</td>
<td>和</td>
<td>5&gt;1 and  1&gt;2</td>
<td>flase</td>
</tr>
<tr>
<td>or</td>
<td>或</td>
<td>5&gt;1  or  1&gt;2</td>
<td>true</td>
</tr>
</tbody></table>
<h3 id="3-5、删除"><a href="#3-5、删除" class="headerlink" title="3.5、删除"></a>3.5、删除</h3><blockquote>
<p>delete命令</p>
</blockquote>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 语法</span></span><br><span class="line"><span class="comment">-- delete from 表名 where [条件]</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">delete</span> <span class="keyword">from</span> student1 <span class="keyword">where</span> id <span class="operator">=</span> <span class="number">1</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 删除student1表</span></span><br><span class="line"><span class="keyword">delete</span> <span class="keyword">from</span> student1</span><br></pre></td></tr></tbody></table></figure>



<blockquote>
<p>truncate 命令</p>
</blockquote>
<p>作用：完全清空一个数据库表，表结构和索引约束不会变</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">truncate</span> student1</span><br></pre></td></tr></tbody></table></figure>



<blockquote>
<p> delete和truncate区别</p>
</blockquote>
<ul>
<li>相同点： 都能删除数据，都不会删除表结构</li>
<li>不同：<ul>
<li>truncate 重新设置自增列，计数器会归零，</li>
<li>truncate 不会影响事物</li>
</ul>
</li>
</ul>
<p>delete删除问题，重启数据库，现象</p>
<ul>
<li>InnoDB   自增列会从1开始（存在内存中，断点即失）</li>
<li>MyISAM   继续从上一个自增量开始（存在文件中，不会丢失）</li>
</ul>
<h2 id="4、DQL查询数据"><a href="#4、DQL查询数据" class="headerlink" title="4、DQL查询数据"></a>4、DQL查询数据</h2><h3 id="4-1-、DQL"><a href="#4-1-、DQL" class="headerlink" title="4.1 、DQL"></a>4.1 、DQL</h3><p>（Data Query LANGUAGE：数据查询语言）</p>
<ul>
<li>所有查询都使用</li>
<li>数据库中最核心的语言，最重要的语句</li>
<li>使用频率最高</li>
</ul>
<h3 id="4-2、select语法"><a href="#4-2、select语法" class="headerlink" title="4.2、select语法"></a>4.2、select语法</h3><blockquote>
<p>select语法</p>
</blockquote>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> [<span class="keyword">ALL</span> <span class="operator">|</span> <span class="keyword">DISTINCT</span>]</span><br><span class="line">{<span class="operator">*</span> <span class="operator">|</span> table.<span class="operator">*</span> <span class="operator">|</span> [table.field1[<span class="keyword">as</span> alias1][,table.field2[<span class="keyword">as</span> alias2]][,...]]}</span><br><span class="line"><span class="keyword">FROM</span> table_name [<span class="keyword">as</span> table_alias]</span><br><span class="line">  [<span class="keyword">left</span> <span class="operator">|</span> <span class="keyword">right</span> <span class="operator">|</span> <span class="keyword">inner</span> <span class="keyword">join</span> table_name2]  <span class="comment">-- 联合查询</span></span><br><span class="line">  [<span class="keyword">WHERE</span> ...]  <span class="comment">-- 指定结果需满足的条件</span></span><br><span class="line">  [<span class="keyword">GROUP</span> <span class="keyword">BY</span> ...]  <span class="comment">-- 指定结果按照哪几个字段来分组</span></span><br><span class="line">  [<span class="keyword">HAVING</span>]  <span class="comment">-- 过滤分组的记录必须满足的次要条件</span></span><br><span class="line">  [<span class="keyword">ORDER</span> <span class="keyword">BY</span> ...]  <span class="comment">-- 指定查询记录按一个或多个条件排序</span></span><br><span class="line">  [LIMIT {[<span class="keyword">offset</span>,]row_count <span class="operator">|</span> row_countOFFSET <span class="keyword">offset</span>}];</span><br><span class="line">   <span class="comment">-- 指定查询的记录从哪条至哪条</span></span><br></pre></td></tr></tbody></table></figure>

<p><strong>注意 : [ ] 括号代表可选的 , { }括号代表必选得</strong></p>
<h3 id="4-3、指定查询字段"><a href="#4-3、指定查询字段" class="headerlink" title="4.3、指定查询字段"></a>4.3、指定查询字段</h3><figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> database if <span class="keyword">not</span> <span class="keyword">exists</span> `school`;</span><br><span class="line"><span class="comment">-- 创建一个school数据库</span></span><br><span class="line">use `school`;<span class="comment">-- 创建学生表</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> if <span class="keyword">exists</span> `student`;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> `student`(</span><br><span class="line">	`studentno` <span class="type">int</span>(<span class="number">4</span>) <span class="keyword">not</span> <span class="keyword">null</span> comment <span class="string">'学号'</span>,</span><br><span class="line">    `loginpwd` <span class="type">varchar</span>(<span class="number">20</span>) <span class="keyword">default</span> <span class="keyword">null</span>,</span><br><span class="line">    `studentname` <span class="type">varchar</span>(<span class="number">20</span>) <span class="keyword">default</span> <span class="keyword">null</span> comment <span class="string">'学生姓名'</span>,</span><br><span class="line">    `sex` tinyint(<span class="number">1</span>) <span class="keyword">default</span> <span class="keyword">null</span> comment <span class="string">'性别，0或1'</span>,</span><br><span class="line">    `gradeid` <span class="type">int</span>(<span class="number">11</span>) <span class="keyword">default</span> <span class="keyword">null</span> comment <span class="string">'年级编号'</span>,</span><br><span class="line">    `phone` <span class="type">varchar</span>(<span class="number">50</span>)  <span class="keyword">null</span> comment <span class="string">'联系电话，允许为空'</span>,</span><br><span class="line">    `address` <span class="type">varchar</span>(<span class="number">255</span>)  <span class="keyword">null</span> comment <span class="string">'地址，允许为空'</span>,</span><br><span class="line">    `borndate` datetime <span class="keyword">default</span> <span class="keyword">null</span> comment <span class="string">'出生时间'</span>,</span><br><span class="line">    `email` <span class="type">varchar</span> (<span class="number">50</span>)  <span class="keyword">null</span> comment <span class="string">'邮箱账号允许为空'</span>,</span><br><span class="line">    `identitycard` <span class="type">varchar</span>(<span class="number">18</span>) <span class="keyword">default</span> <span class="keyword">null</span> comment <span class="string">'身份证号'</span>,</span><br><span class="line">    <span class="keyword">primary</span> key (`studentno`),</span><br><span class="line">    <span class="keyword">unique</span> key `identitycard`(`identitycard`),</span><br><span class="line">    key `email` (`email`)</span><br><span class="line">)engine<span class="operator">=</span>myisam <span class="keyword">default</span> charset<span class="operator">=</span>utf8;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 创建年级表</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> if <span class="keyword">exists</span> `grade`;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> `grade`(</span><br><span class="line">	`gradeid` <span class="type">int</span>(<span class="number">11</span>) <span class="keyword">not</span> <span class="keyword">null</span> auto_increment comment <span class="string">'年级编号'</span>,</span><br><span class="line">  `gradename` <span class="type">varchar</span>(<span class="number">50</span>) <span class="keyword">not</span> <span class="keyword">null</span> comment <span class="string">'年级名称'</span>,</span><br><span class="line">    <span class="keyword">primary</span> key (`gradeid`)</span><br><span class="line">) engine<span class="operator">=</span>innodb auto_increment <span class="operator">=</span> <span class="number">6</span> <span class="keyword">default</span> charset <span class="operator">=</span> utf8;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 创建科目表</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> if <span class="keyword">exists</span> `subject`;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> `subject`(</span><br><span class="line">	`subjectno`<span class="type">int</span>(<span class="number">11</span>) <span class="keyword">not</span> <span class="keyword">null</span> auto_increment comment <span class="string">'课程编号'</span>,</span><br><span class="line">    `subjectname` <span class="type">varchar</span>(<span class="number">50</span>) <span class="keyword">default</span> <span class="keyword">null</span> comment <span class="string">'课程名称'</span>,</span><br><span class="line">    `classhour` <span class="type">int</span>(<span class="number">4</span>) <span class="keyword">default</span> <span class="keyword">null</span> comment <span class="string">'学时'</span>,</span><br><span class="line">    `gradeid` <span class="type">int</span>(<span class="number">4</span>) <span class="keyword">default</span> <span class="keyword">null</span> comment <span class="string">'年级编号'</span>,</span><br><span class="line">    <span class="keyword">primary</span> key (`subjectno`)</span><br><span class="line">)engine <span class="operator">=</span> innodb auto_increment <span class="operator">=</span> <span class="number">19</span> <span class="keyword">default</span> charset <span class="operator">=</span> utf8;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 创建成绩表</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> if <span class="keyword">exists</span> `<span class="keyword">result</span>`;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> `<span class="keyword">result</span>`(</span><br><span class="line">	`studentno` <span class="type">int</span>(<span class="number">4</span>) <span class="keyword">not</span> <span class="keyword">null</span> comment <span class="string">'学号'</span>,</span><br><span class="line">    `subjectno` <span class="type">int</span>(<span class="number">4</span>) <span class="keyword">not</span> <span class="keyword">null</span> comment <span class="string">'课程编号'</span>,</span><br><span class="line">    `examdate` datetime <span class="keyword">not</span> <span class="keyword">null</span> comment <span class="string">'考试日期'</span>,</span><br><span class="line">    `studentresult` <span class="type">int</span> (<span class="number">4</span>) <span class="keyword">not</span> <span class="keyword">null</span> comment <span class="string">'考试成绩'</span>,</span><br><span class="line">    key `subjectno` (`subjectno`)</span><br><span class="line">)engine <span class="operator">=</span> innodb <span class="keyword">default</span> charset <span class="operator">=</span> utf8;</span><br></pre></td></tr></tbody></table></figure>



<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查询全部学生</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询指定字段</span></span><br><span class="line"><span class="keyword">select</span> studentno,studentname <span class="keyword">from</span> student</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 别名</span></span><br><span class="line"><span class="keyword">select</span> studentno <span class="keyword">as</span> 学号, studentname <span class="keyword">as</span> 学生姓名 <span class="keyword">from</span> student <span class="keyword">as</span> s</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 函数</span></span><br><span class="line"><span class="keyword">select</span> concat(<span class="string">'姓名：'</span>, studentname) <span class="keyword">as</span> 新名字 <span class="keyword">from</span> student</span><br></pre></td></tr></tbody></table></figure>



<blockquote>
<p>去重 distinct</p>
</blockquote>
<p>作用： 去除select查询出来结果中重复的数据</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> studentno <span class="keyword">from</span> <span class="keyword">result</span></span><br></pre></td></tr></tbody></table></figure>



<blockquote>
<p>数据库的列（表达式)</p>
</blockquote>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> version()			<span class="comment">--查询系统版本（函数）</span></span><br><span class="line"><span class="keyword">select</span> <span class="number">1</span><span class="operator">+</span><span class="number">5</span><span class="operator">*</span><span class="number">8</span> 						<span class="comment">-- 用来计算（表达式）</span></span><br><span class="line"><span class="keyword">select</span> @<span class="variable">@auto</span>_increment_increment    <span class="comment">-- 查询自增的步长（变量）</span></span><br></pre></td></tr></tbody></table></figure>

<p>数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量</p>
<h3 id="4-4-、where条件字句"><a href="#4-4-、where条件字句" class="headerlink" title="4.4 、where条件字句"></a>4.4 、where条件字句</h3><p>作用：检索数据中符合条件的值</p>
<blockquote>
<p>逻辑运算符</p>
</blockquote>
<p>搜索条件可由一个或多个逻辑表达式组成 ，结果一般为真或假。</p>
<table>
<thead>
<tr>
<th>运算法</th>
<th>语法</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>and  &amp;&amp;</td>
<td>a and b   a&amp;&amp;b</td>
<td>逻辑与，两个都为真，结果为真</td>
</tr>
<tr>
<td>or      ||</td>
<td>a or b     a || b</td>
<td>逻辑或，一个为真，结果为真</td>
</tr>
<tr>
<td>not    !</td>
<td>not a     !a</td>
<td>逻辑非，真为假，假为真</td>
</tr>
</tbody></table>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查询分数在95到100之间的学生</span></span><br><span class="line"><span class="keyword">select</span> studentno,studentresult <span class="keyword">from</span> <span class="keyword">result</span> </span><br><span class="line"><span class="keyword">where</span> studentresult <span class="operator">&gt;</span><span class="number">95</span> <span class="keyword">and</span> studentresult <span class="operator">&lt;=</span> <span class="number">100</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> studentno,studentresult <span class="keyword">from</span> <span class="keyword">result</span> </span><br><span class="line"><span class="keyword">where</span> studentresult <span class="keyword">between</span> <span class="number">95</span> <span class="keyword">and</span> <span class="number">100</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询1号学生之外的同学成绩</span></span><br><span class="line"><span class="keyword">select</span> studentno,studentresult <span class="keyword">from</span> <span class="keyword">result</span> <span class="keyword">where</span> <span class="keyword">not</span> studentno <span class="operator">=</span> <span class="number">1</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> studentno,studentresult <span class="keyword">from</span> <span class="keyword">result</span> <span class="keyword">where</span> studentno <span class="operator">!=</span> <span class="number">1</span></span><br></pre></td></tr></tbody></table></figure>



<blockquote>
<p>模糊查询：比较运算符</p>
</blockquote>
<table>
<thead>
<tr>
<th>运算符</th>
<th>语法</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>is null</td>
<td>a is null</td>
<td>如果操作符为null，则结果为真</td>
</tr>
<tr>
<td>is not null</td>
<td>a is not null</td>
<td>如果操作符不为null，则结果为真</td>
</tr>
<tr>
<td>between</td>
<td>a between  b and c</td>
<td>若a在b和c之间，则结果为真</td>
</tr>
<tr>
<td><strong>like</strong></td>
<td>a like b</td>
<td>SQL匹配，如果a匹配b，则结果为真</td>
</tr>
<tr>
<td>in</td>
<td>a in (a1,a2,a3,…….)</td>
<td>若a等于a1,a2,a3中的某一个，则结果为真</td>
</tr>
</tbody></table>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查询姓刘的同学的学号及姓名</span></span><br><span class="line"><span class="comment">-- like结合使用的通配符 : % (代表0到任意个字符) _ (一个字符)</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,studentname <span class="keyword">FROM</span> student</span><br><span class="line"><span class="keyword">WHERE</span> studentname <span class="keyword">LIKE</span> <span class="string">'刘%'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询姓刘的同学,后面只有一个字的</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,studentname <span class="keyword">FROM</span> student</span><br><span class="line"><span class="keyword">WHERE</span> studentname <span class="keyword">LIKE</span> <span class="string">'刘_'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询姓刘的同学,后面只有两个字的</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,studentname <span class="keyword">FROM</span> student</span><br><span class="line"><span class="keyword">WHERE</span> studentname <span class="keyword">LIKE</span> <span class="string">'刘__'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询姓名中含有 文 字的</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,studentname <span class="keyword">FROM</span> student</span><br><span class="line"><span class="keyword">WHERE</span> studentname <span class="keyword">LIKE</span> <span class="string">'%文%'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询学号为1000,1001,1002的学生姓名</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,studentname <span class="keyword">FROM</span> student</span><br><span class="line"><span class="keyword">WHERE</span> studentno <span class="keyword">IN</span> (<span class="number">1000</span>,<span class="number">1001</span>,<span class="number">1002</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询电子邮件没有填写的同学</span></span><br><span class="line"><span class="comment">-- 不能直接写=NULL , 这是代表错误的 , 用 is null</span></span><br><span class="line"><span class="keyword">SELECT</span> studentname <span class="keyword">FROM</span> student</span><br><span class="line"><span class="keyword">WHERE</span> email <span class="keyword">IS</span> <span class="keyword">NULL</span>;</span><br></pre></td></tr></tbody></table></figure>



<h3 id="4-5、联表查询"><a href="#4-5、联表查询" class="headerlink" title="4.5、联表查询"></a>4.5、联表查询</h3><blockquote>
<p>join</p>
</blockquote>
<table>
<thead>
<tr>
<th>操作</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>Inner join</td>
<td>如果表中有至少一个匹配，则返回行</td>
</tr>
<tr>
<td>left join</td>
<td>即使右表没有匹配，也从左表中返回所有的行</td>
</tr>
<tr>
<td>right join</td>
<td>即使左表没有匹配，也从右表中返回所有的行</td>
</tr>
</tbody></table>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">连接查询</span></span><br><span class="line"><span class="comment">   如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询</span></span><br><span class="line"><span class="comment">内连接 inner join</span></span><br><span class="line"><span class="comment">   查询两个表中的结果集中的交集</span></span><br><span class="line"><span class="comment">外连接 outer join</span></span><br><span class="line"><span class="comment">   左外连接 left join</span></span><br><span class="line"><span class="comment">       (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)</span></span><br><span class="line"><span class="comment">   右外连接 right join</span></span><br><span class="line"><span class="comment">       (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)</span></span><br><span class="line"><span class="comment">       </span></span><br><span class="line"><span class="comment">等值连接和非等值连接</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">自连接</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="comment">-- 内连接</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectno,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> r.studentno <span class="operator">=</span> s.studentno</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 右连接(也可实现)</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectno,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">RIGHT</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> r.studentno <span class="operator">=</span> s.studentno</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 左连接 (查询了所有同学,不考试的也会查出来)</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectno,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">LEFT</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> r.studentno <span class="operator">=</span> s.studentno</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查一下缺考的同学(左连接应用场景)</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectno,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">LEFT</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> r.studentno <span class="operator">=</span> s.studentno</span><br><span class="line"><span class="keyword">WHERE</span> StudentResult <span class="keyword">IS</span> <span class="keyword">NULL</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectname,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> r.studentno <span class="operator">=</span> s.studentno</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> `subject` sub</span><br><span class="line"><span class="keyword">ON</span> sub.subjectno <span class="operator">=</span> r.subjectno</span><br></pre></td></tr></tbody></table></figure>

<blockquote>
<p>自连接</p>
</blockquote>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">自连接</span></span><br><span class="line"><span class="comment">   数据表与自身进行连接</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中</span></span><br><span class="line"><span class="comment">    查询父栏目名称和其他子栏目名称</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 创建一个表</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `category` (</span><br><span class="line">`categoryid` <span class="type">INT</span>(<span class="number">10</span>) UNSIGNED <span class="keyword">NOT</span> <span class="keyword">NULL</span> AUTO_INCREMENT COMMENT <span class="string">'主题id'</span>,</span><br><span class="line">`pid` <span class="type">INT</span>(<span class="number">10</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span> COMMENT <span class="string">'父id'</span>,</span><br><span class="line">`categoryName` <span class="type">VARCHAR</span>(<span class="number">50</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span> COMMENT <span class="string">'主题名字'</span>,</span><br><span class="line"><span class="keyword">PRIMARY</span> KEY (`categoryid`)</span><br><span class="line">) ENGINE<span class="operator">=</span>INNODB AUTO_INCREMENT<span class="operator">=</span><span class="number">9</span> <span class="keyword">DEFAULT</span> CHARSET<span class="operator">=</span>utf8</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 插入数据</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `category` (`categoryid`, `pid`, `categoryName`)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'2'</span>,<span class="string">'1'</span>,<span class="string">'信息技术'</span>),</span><br><span class="line">(<span class="string">'3'</span>,<span class="string">'1'</span>,<span class="string">'软件开发'</span>),</span><br><span class="line">(<span class="string">'4'</span>,<span class="string">'3'</span>,<span class="string">'数据库'</span>),</span><br><span class="line">(<span class="string">'5'</span>,<span class="string">'1'</span>,<span class="string">'美术设计'</span>),</span><br><span class="line">(<span class="string">'6'</span>,<span class="string">'3'</span>,<span class="string">'web开发'</span>),</span><br><span class="line">(<span class="string">'7'</span>,<span class="string">'5'</span>,<span class="string">'ps技术'</span>),</span><br><span class="line">(<span class="string">'8'</span>,<span class="string">'2'</span>,<span class="string">'办公信息'</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)</span></span><br><span class="line"><span class="comment">-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)</span></span><br><span class="line"><span class="keyword">SELECT</span> a.categoryName <span class="keyword">AS</span> <span class="string">'父栏目'</span>,b.categoryName <span class="keyword">AS</span> <span class="string">'子栏目'</span></span><br><span class="line"><span class="keyword">FROM</span> category <span class="keyword">AS</span> a,category <span class="keyword">AS</span> b</span><br><span class="line"><span class="keyword">WHERE</span> a.`categoryid`<span class="operator">=</span>b.`pid`</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectname,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> r.studentno <span class="operator">=</span> s.studentno</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> `subject` sub</span><br><span class="line"><span class="keyword">ON</span> sub.subjectno <span class="operator">=</span> r.subjectno</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询学员及所属的年级(学号,学生姓名,年级名)</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno <span class="keyword">AS</span> 学号,studentname <span class="keyword">AS</span> 学生姓名,gradename <span class="keyword">AS</span> 年级名称</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> grade g</span><br><span class="line"><span class="keyword">ON</span> s.`GradeId` <span class="operator">=</span> g.`GradeID`</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询科目及所属的年级(科目名称,年级名称)</span></span><br><span class="line"><span class="keyword">SELECT</span> subjectname <span class="keyword">AS</span> 科目名称,gradename <span class="keyword">AS</span> 年级名称</span><br><span class="line"><span class="keyword">FROM</span> SUBJECT sub</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> grade g</span><br><span class="line"><span class="keyword">ON</span> sub.gradeid <span class="operator">=</span> g.gradeid</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询 操作系统 的所有考试结果(学号 学生姓名 科目名称 成绩)</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectname,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> r.studentno <span class="operator">=</span> s.studentno</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> `subject` sub</span><br><span class="line"><span class="keyword">ON</span> r.subjectno <span class="operator">=</span> sub.subjectno</span><br><span class="line"><span class="keyword">WHERE</span> subjectname<span class="operator">=</span><span class="string">'操作系统'</span></span><br></pre></td></tr></tbody></table></figure>



<h3 id="4-6、分页和排序"><a href="#4-6、分页和排序" class="headerlink" title="4.6、分页和排序"></a>4.6、分页和排序</h3><figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*============== 排序 ================</span></span><br><span class="line"><span class="comment">语法 : ORDER BY</span></span><br><span class="line"><span class="comment">   ORDER BY 语句用于根据指定的列对结果集进行排序。</span></span><br><span class="line"><span class="comment">   ORDER BY 语句默认按照ASC升序对记录进行排序。</span></span><br><span class="line"><span class="comment">   如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。</span></span><br><span class="line"><span class="comment">   </span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询 数据结构 的所有考试结果(学号 学生姓名 科目名称 成绩)</span></span><br><span class="line"><span class="comment">-- 按成绩降序排序</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectname,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> r.studentno <span class="operator">=</span> s.studentno</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> `subject` sub</span><br><span class="line"><span class="keyword">ON</span> r.subjectno <span class="operator">=</span> sub.subjectno</span><br><span class="line"><span class="keyword">WHERE</span> subjectname<span class="operator">=</span><span class="string">'数据结构'</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> StudentResult <span class="keyword">DESC</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/*============== 分页 ================</span></span><br><span class="line"><span class="comment">语法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset</span></span><br><span class="line"><span class="comment">好处 : (用户体验,网络传输,查询压力)</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">推导:</span></span><br><span class="line"><span class="comment">   第一页 : limit 0,5</span></span><br><span class="line"><span class="comment">   第二页 : limit 5,5</span></span><br><span class="line"><span class="comment">   第三页 : limit 10,5</span></span><br><span class="line"><span class="comment">   ......</span></span><br><span class="line"><span class="comment">   第N页 : limit (pageNo-1)*pageSzie,pageSzie</span></span><br><span class="line"><span class="comment">   [pageNo:页码,pageSize:单页面显示条数]</span></span><br><span class="line"><span class="comment">   </span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 每页显示5条数据</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectname,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> r.studentno <span class="operator">=</span> s.studentno</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> `subject` sub</span><br><span class="line"><span class="keyword">ON</span> r.subjectno <span class="operator">=</span> sub.subjectno</span><br><span class="line"><span class="keyword">WHERE</span> subjectname<span class="operator">=</span><span class="string">'数据结构'</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> StudentResult <span class="keyword">DESC</span> , studentno</span><br><span class="line">LIMIT <span class="number">0</span>,<span class="number">5</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname,subjectname,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> r.studentno <span class="operator">=</span> s.studentno</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> `subject` sub</span><br><span class="line"><span class="keyword">ON</span> r.subjectno <span class="operator">=</span> sub.subjectno</span><br><span class="line"><span class="keyword">WHERE</span> subjectname<span class="operator">=</span><span class="string">'数据结构'</span> <span class="keyword">and</span> studentresult<span class="operator">&gt;</span><span class="number">99</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> StudentResult <span class="keyword">DESC</span></span><br><span class="line">LIMIT <span class="number">0</span>,<span class="number">10</span></span><br></pre></td></tr></tbody></table></figure>



<h3 id="4-7、子查询"><a href="#4-7、子查询" class="headerlink" title="4.7、子查询"></a>4.7、子查询</h3><p>where（这个值是计算出来的）</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*============== 子查询 ================</span></span><br><span class="line"><span class="comment">什么是子查询?</span></span><br><span class="line"><span class="comment">   在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句</span></span><br><span class="line"><span class="comment">   嵌套查询可由多个子查询组成,求解的方式是由里及外;</span></span><br><span class="line"><span class="comment">   子查询返回的结果一般都是集合,故而建议使用IN关键字;</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询 数据结构 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列</span></span><br><span class="line"><span class="comment">-- 方法一:使用连接查询</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,r.subjectno,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> `subject` sub</span><br><span class="line"><span class="keyword">ON</span> r.`SubjectNo`<span class="operator">=</span>sub.`SubjectNo`</span><br><span class="line"><span class="keyword">WHERE</span> subjectname <span class="operator">=</span> <span class="string">'数据结构'</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> studentresult <span class="keyword">DESC</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 方法二:使用子查询(执行顺序:由里及外)</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,subjectno,StudentResult</span><br><span class="line"><span class="keyword">FROM</span> <span class="keyword">result</span></span><br><span class="line"><span class="keyword">WHERE</span> subjectno<span class="operator">=</span>(</span><br><span class="line">   <span class="keyword">SELECT</span> subjectno <span class="keyword">FROM</span> `subject`</span><br><span class="line">   <span class="keyword">WHERE</span> subjectname <span class="operator">=</span> <span class="string">'数据结构'</span></span><br><span class="line">)</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> studentresult <span class="keyword">DESC</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询课程为 高等数学 且分数不小于80分的学生的学号和姓名</span></span><br><span class="line"><span class="comment">-- 方法一:使用连接查询</span></span><br><span class="line"><span class="keyword">SELECT</span> s.studentno,studentname</span><br><span class="line"><span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r</span><br><span class="line"><span class="keyword">ON</span> s.`StudentNo` <span class="operator">=</span> r.`StudentNo`</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> `subject` sub</span><br><span class="line"><span class="keyword">ON</span> sub.`SubjectNo` <span class="operator">=</span> r.`SubjectNo`</span><br><span class="line"><span class="keyword">WHERE</span> subjectname <span class="operator">=</span> <span class="string">'高等数学'</span> <span class="keyword">AND</span> StudentResult<span class="operator">&gt;=</span><span class="number">80</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 方法二:使用连接查询+子查询</span></span><br><span class="line"><span class="comment">-- 分数不小于80分的学生的学号和姓名</span></span><br><span class="line"><span class="keyword">SELECT</span> r.studentno,studentname <span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r <span class="keyword">ON</span> s.`StudentNo`<span class="operator">=</span>r.`StudentNo`</span><br><span class="line"><span class="keyword">WHERE</span> StudentResult<span class="operator">&gt;=</span><span class="number">80</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 在上面SQL基础上,添加需求:课程为 高等数学</span></span><br><span class="line"><span class="keyword">SELECT</span> r.studentno,studentname <span class="keyword">FROM</span> student s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">result</span> r <span class="keyword">ON</span> s.`StudentNo`<span class="operator">=</span>r.`StudentNo`</span><br><span class="line"><span class="keyword">WHERE</span> StudentResult<span class="operator">&gt;=</span><span class="number">80</span> <span class="keyword">AND</span> subjectno<span class="operator">=</span>(</span><br><span class="line">   <span class="keyword">SELECT</span> subjectno <span class="keyword">FROM</span> `subject`</span><br><span class="line">   <span class="keyword">WHERE</span> subjectname <span class="operator">=</span> <span class="string">'高等数学'</span></span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 方法三:使用子查询</span></span><br><span class="line"><span class="comment">-- 分步写简单sql语句,然后将其嵌套起来</span></span><br><span class="line"><span class="keyword">SELECT</span> studentno,studentname <span class="keyword">FROM</span> student <span class="keyword">WHERE</span> studentno <span class="keyword">IN</span>(</span><br><span class="line">   <span class="keyword">SELECT</span> studentno <span class="keyword">FROM</span> <span class="keyword">result</span> <span class="keyword">WHERE</span> StudentResult<span class="operator">&gt;=</span><span class="number">80</span> <span class="keyword">AND</span> subjectno<span class="operator">=</span>(</span><br><span class="line">       <span class="keyword">SELECT</span> subjectno <span class="keyword">FROM</span> `subject` <span class="keyword">WHERE</span> subjectname <span class="operator">=</span> <span class="string">'高等数学'</span></span><br><span class="line">  )</span><br><span class="line">)</span><br></pre></td></tr></tbody></table></figure>

<h2 id="5、MySQL函数"><a href="#5、MySQL函数" class="headerlink" title="5、MySQL函数"></a>5、MySQL函数</h2><h3 id="5-1、常用函数"><a href="#5-1、常用函数" class="headerlink" title="5.1、常用函数"></a>5.1、常用函数</h3><p>数据函数</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="built_in">ABS</span>(<span class="number">-8</span>);  <span class="comment">/*绝对值*/</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="built_in">CEILING</span>(<span class="number">9.4</span>); <span class="comment">/*向上取整*/</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="built_in">FLOOR</span>(<span class="number">9.4</span>);   <span class="comment">/*向下取整*/</span></span><br><span class="line"><span class="keyword">SELECT</span> RAND();  <span class="comment">/*随机数,返回一个0-1之间的随机数*/</span></span><br><span class="line"><span class="keyword">SELECT</span> SIGN(<span class="number">0</span>); <span class="comment">/*符号函数: 负数返回-1,正数返回1,0返回0*/</span></span><br></pre></td></tr></tbody></table></figure>

<p>字符串函数</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">CHAR_LENGTH</span>(<span class="string">'狂神说坚持就能成功'</span>); <span class="comment">/*返回字符串包含的字符数*/</span></span><br><span class="line"><span class="keyword">SELECT</span> CONCAT(<span class="string">'我'</span>,<span class="string">'爱'</span>,<span class="string">'程序'</span>);  <span class="comment">/*合并字符串,参数可以有多个*/</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">INSERT</span>(<span class="string">'我爱编程helloworld'</span>,<span class="number">1</span>,<span class="number">2</span>,<span class="string">'超级热爱'</span>);  <span class="comment">/*替换字符串,从某个位置开始替换某个长度*/</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="built_in">LOWER</span>(<span class="string">'KuangShen'</span>); <span class="comment">/*小写*/</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="built_in">UPPER</span>(<span class="string">'KuangShen'</span>); <span class="comment">/*大写*/</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">LEFT</span>(<span class="string">'hello,world'</span>,<span class="number">5</span>);   <span class="comment">/*从左边截取*/</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">RIGHT</span>(<span class="string">'hello,world'</span>,<span class="number">5</span>);  <span class="comment">/*从右边截取*/</span></span><br><span class="line"><span class="keyword">SELECT</span> REPLACE(<span class="string">'狂神说坚持就能成功'</span>,<span class="string">'坚持'</span>,<span class="string">'努力'</span>);  <span class="comment">/*替换字符串*/</span></span><br><span class="line"><span class="keyword">SELECT</span> SUBSTR(<span class="string">'狂神说坚持就能成功'</span>,<span class="number">4</span>,<span class="number">6</span>); <span class="comment">/*截取字符串,开始和长度*/</span></span><br><span class="line"><span class="keyword">SELECT</span> REVERSE(<span class="string">'狂神说坚持就能成功'</span>); <span class="comment">/*反转</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">-- 查询姓周的同学,改成邹</span></span><br><span class="line"><span class="comment">SELECT REPLACE(studentname,'周','邹') AS 新名字</span></span><br><span class="line"><span class="comment">FROM student WHERE studentname LIKE '周%';</span></span><br></pre></td></tr></tbody></table></figure>

<p>日期和时间函数</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="built_in">CURRENT_DATE</span>();   <span class="comment">/*获取当前日期*/</span></span><br><span class="line"><span class="keyword">SELECT</span> CURDATE();   <span class="comment">/*获取当前日期*/</span></span><br><span class="line"><span class="keyword">SELECT</span> NOW();   <span class="comment">/*获取当前日期和时间*/</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="built_in">LOCALTIME</span>();   <span class="comment">/*获取当前日期和时间*/</span></span><br><span class="line"><span class="keyword">SELECT</span> SYSDATE();   <span class="comment">/*获取当前日期和时间*/</span></span><br><span class="line"></span><br><span class="line"> <span class="comment">-- 获取年月日,时分秒</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">YEAR</span>(NOW());</span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">MONTH</span>(NOW());</span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">DAY</span>(NOW());</span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">HOUR</span>(NOW());</span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">MINUTE</span>(NOW());</span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">SECOND</span>(NOW());</span><br></pre></td></tr></tbody></table></figure>

<p>系统信息函数</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> VERSION();  <span class="comment">/*版本*/</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">USER</span>();     <span class="comment">/*用户*/</span></span><br></pre></td></tr></tbody></table></figure>

<h3 id="5-2、聚合函数"><a href="#5-2、聚合函数" class="headerlink" title="5.2、聚合函数"></a>5.2、聚合函数</h3><table>
<thead>
<tr>
<th>函数名称</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>count()</td>
<td>返回满足Select条件的记录总和数，如 select count(*) 【不建议使用 *，效率低】</td>
</tr>
<tr>
<td>sum()</td>
<td>返回数字字段或表达式列作统计，返回一列的总和。</td>
</tr>
<tr>
<td>max()</td>
<td>可以为数值字段，字符字段或表达式列作统计，返回最大的值</td>
</tr>
<tr>
<td>min()</td>
<td>可以为数值字段，字符字段或表达式列作统计，返回最小的值</td>
</tr>
<tr>
<td>avg()</td>
<td>通常为数值字段或表达列作统计，返回一列的平均值</td>
</tr>
</tbody></table>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 聚合函数</span></span><br><span class="line"> <span class="comment">/*COUNT:非空的*/</span></span><br><span class="line"> <span class="keyword">SELECT</span> <span class="built_in">COUNT</span>(studentname) <span class="keyword">FROM</span> student;</span><br><span class="line"> <span class="keyword">SELECT</span> <span class="built_in">COUNT</span>(<span class="operator">*</span>) <span class="keyword">FROM</span> student;</span><br><span class="line"> <span class="keyword">SELECT</span> <span class="built_in">COUNT</span>(<span class="number">1</span>) <span class="keyword">FROM</span> student;  <span class="comment">/*推荐*/</span></span><br><span class="line"> </span><br><span class="line"> <span class="comment">-- 从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询。</span></span><br><span class="line"> <span class="comment">-- count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。</span></span><br><span class="line"> <span class="comment">-- count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null 的记录；</span></span><br><span class="line"> <span class="comment">-- count(1) 用1代表代码行，在统计结果的时候，包含字段为null 的记录 。</span></span><br><span class="line"> <span class="comment">/*</span></span><br><span class="line"><span class="comment"> 很多人认为count(1)执行的效率会比count(*)高，原因是count(*)会存在全表扫描，而count(1)可以针对一个字段进行查询。其实不然，count(1)和count(*)都会对全表进行扫描，统计所有记录的条数，包括那些为null的记录，因此，它们的效率可以说是相差无几。而count(字段)则与前两者不同，它会统计该字段不为null的记录条数。</span></span><br><span class="line"><span class="comment"> </span></span><br><span class="line"><span class="comment"> 下面它们之间的一些对比：</span></span><br><span class="line"><span class="comment"> </span></span><br><span class="line"><span class="comment"> 1）在表没有主键时，count(1)比count(*)快</span></span><br><span class="line"><span class="comment"> 2）有主键时，主键作为计算条件，count(主键)效率最高；</span></span><br><span class="line"><span class="comment"> 3）若表格只有一个字段，则count(*)效率较高。</span></span><br><span class="line"><span class="comment"> */</span></span><br><span class="line"> </span><br><span class="line"> <span class="keyword">SELECT</span> <span class="built_in">SUM</span>(StudentResult) <span class="keyword">AS</span> 总和 <span class="keyword">FROM</span> <span class="keyword">result</span>;</span><br><span class="line"> <span class="keyword">SELECT</span> <span class="built_in">AVG</span>(StudentResult) <span class="keyword">AS</span> 平均分 <span class="keyword">FROM</span> <span class="keyword">result</span>;</span><br><span class="line"> <span class="keyword">SELECT</span> <span class="built_in">MAX</span>(StudentResult) <span class="keyword">AS</span> 最高分 <span class="keyword">FROM</span> <span class="keyword">result</span>;</span><br><span class="line"> <span class="keyword">SELECT</span> <span class="built_in">MIN</span>(StudentResult) <span class="keyword">AS</span> 最低分 <span class="keyword">FROM</span> <span class="keyword">result</span>;</span><br></pre></td></tr></tbody></table></figure>



<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查询不同课程的平均分,最高分,最低分</span></span><br><span class="line"><span class="comment">-- 前提:根据不同的课程进行分组</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span> subjectname,<span class="built_in">AVG</span>(studentresult) <span class="keyword">AS</span> 平均分,<span class="built_in">MAX</span>(StudentResult) <span class="keyword">AS</span> 最高分,<span class="built_in">MIN</span>(StudentResult) <span class="keyword">AS</span> 最低分</span><br><span class="line"><span class="keyword">FROM</span> <span class="keyword">result</span> <span class="keyword">AS</span> r</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> `subject` <span class="keyword">AS</span> s</span><br><span class="line"><span class="keyword">ON</span> r.subjectno <span class="operator">=</span> s.subjectno</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> r.subjectno</span><br><span class="line"><span class="keyword">HAVING</span> 平均分<span class="operator">&gt;</span><span class="number">80</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">where写在group by前面.</span></span><br><span class="line"><span class="comment">要是放在分组后面的筛选</span></span><br><span class="line"><span class="comment">要使用HAVING..</span></span><br><span class="line"><span class="comment">因为having是从前面筛选的字段再筛选，而where是从数据表中的&gt;字段直接进行的筛选的</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></tbody></table></figure>



<h3 id="5-3、数据库级别的MD5加密"><a href="#5-3、数据库级别的MD5加密" class="headerlink" title="5.3、数据库级别的MD5加密"></a>5.3、数据库级别的MD5加密</h3><p><strong>一、MD5简介</strong></p>
<p>MD5即Message-Digest Algorithm 5（信息-摘要算法5），用于确保信息传输完整一致。是计算机广泛使用的杂凑算法之一（又译摘要算法、哈希算法），主流编程语言普遍已有MD5实现。将数据（如汉字）运算为另一固定长度值，是杂凑算法的基础原理，MD5的前身有MD2、MD3和MD4。</p>
<p><strong>二、实现数据加密</strong></p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `testmd5` (</span><br><span class="line">  `id` <span class="type">INT</span>(<span class="number">4</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">  `name` <span class="type">VARCHAR</span>(<span class="number">20</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">  `pwd` <span class="type">VARCHAR</span>(<span class="number">50</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">  <span class="keyword">PRIMARY</span> KEY (`id`)</span><br><span class="line"> ) ENGINE<span class="operator">=</span>INNODB <span class="keyword">DEFAULT</span> CHARSET<span class="operator">=</span>utf8</span><br></pre></td></tr></tbody></table></figure>

<p>插入一些数据</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> testmd5 <span class="keyword">VALUES</span>(<span class="number">1</span>,<span class="string">'chen'</span>,<span class="string">'123456'</span>),(<span class="number">2</span>,<span class="string">'root'</span>,<span class="string">'456789'</span>)</span><br></pre></td></tr></tbody></table></figure>

<p>如果我们要对pwd这一列数据进行加密，语法是：</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">update</span> testmd5 <span class="keyword">set</span> pwd <span class="operator">=</span> md5(pwd);</span><br></pre></td></tr></tbody></table></figure>

<p>如果单独对某个用户(如kuangshen)的密码加密：</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> testmd5 <span class="keyword">VALUES</span>(<span class="number">3</span>,<span class="string">'kuangshen2'</span>,<span class="string">'123456'</span>)</span><br><span class="line"><span class="keyword">update</span> testmd5 <span class="keyword">set</span> pwd <span class="operator">=</span> md5(pwd) <span class="keyword">where</span> name <span class="operator">=</span> <span class="string">'kuangshen2'</span>;</span><br></pre></td></tr></tbody></table></figure>

<p>插入新的数据自动加密</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> testmd5 <span class="keyword">VALUES</span>(<span class="number">4</span>,<span class="string">'kuangshen3'</span>,md5(<span class="string">'123456'</span>));</span><br></pre></td></tr></tbody></table></figure>

<p>查询登录用户信息（md5对比使用，查看用户输入加密后的密码进行比对）</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> testmd5 <span class="keyword">WHERE</span> `name`<span class="operator">=</span><span class="string">'kuangshen'</span> <span class="keyword">AND</span> pwd<span class="operator">=</span>MD5(<span class="string">'123456'</span>);</span><br></pre></td></tr></tbody></table></figure>

<h2 id="6、事务"><a href="#6、事务" class="headerlink" title="6、事务"></a>6、事务</h2><blockquote>
<p>什么是事物</p>
</blockquote>
<p>要么都成功，要么都失败</p>
<hr>
<p>1、SQL执行   A给B转账   A 1000 —-&gt;200  B 200</p>
<p>2、SQL执行   B收到A的钱   A 800   —&gt; B 400</p>
<hr>
<p>将一组SQL放在一个批次中执行</p>
<blockquote>
<p>事务原则：ACID原则 原子性，一致性，隔离性，持久性   （脏读，幻读…….）</p>
</blockquote>
<p><strong>原子性（Atomicity）</strong></p>
<p>要么都成功，要么都失败</p>
<p><strong>一致性（Consistency）</strong></p>
<p>事物前后数据完整性要保持一致，1000</p>
<p>持久性（Durability）</p>
<p>事物一旦提交则不可逆，被持久化到数据库中</p>
<p><strong>隔离性（Isolation）</strong></p>
<p>事物的隔离性是多个用户并发访问数据库时，数据库为每个用户开启的事务不能被其他事务干扰。</p>
<blockquote>
<p>隔离导致的问题</p>
</blockquote>
<p><strong>脏读</strong></p>
<p>一个事物读取了另一个事务未提交的数据</p>
<p><strong>不可重复读</strong></p>
<p>在一个事物内读取表中某一行数据，多次读取结果不同</p>
<p><strong>虚读（幻读）</strong></p>
<p>一个事物内读取到了别的事物插入的数据，导致前后读取不一致</p>
<blockquote>
<p>基本语法</p>
</blockquote>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 使用set语句来改变自动提交模式</span></span><br><span class="line"><span class="keyword">SET</span> autocommit <span class="operator">=</span> <span class="number">0</span>;   <span class="comment">/*关闭*/</span></span><br><span class="line"><span class="keyword">SET</span> autocommit <span class="operator">=</span> <span class="number">1</span>;   <span class="comment">/*开启*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 注意:</span></span><br><span class="line"><span class="comment">--- 1.MySQL中默认是自动提交</span></span><br><span class="line"><span class="comment">--- 2.使用事务时应先关闭自动提交</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 开始一个事务,标记事务的起始点</span></span><br><span class="line"><span class="keyword">START</span> TRANSACTION  </span><br><span class="line"></span><br><span class="line"><span class="comment">-- 提交一个事务给数据库</span></span><br><span class="line"><span class="keyword">COMMIT</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 将事务回滚,数据回到本次事务的初始状态</span></span><br><span class="line"><span class="keyword">ROLLBACK</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 还原MySQL数据库的自动提交</span></span><br><span class="line"><span class="keyword">SET</span> autocommit <span class="operator">=</span><span class="number">1</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 保存点</span></span><br><span class="line"><span class="keyword">SAVEPOINT</span> 保存点名称 <span class="comment">-- 设置一个事务保存点</span></span><br><span class="line"><span class="keyword">ROLLBACK</span> <span class="keyword">TO</span> <span class="keyword">SAVEPOINT</span> 保存点名称 <span class="comment">-- 回滚到保存点</span></span><br><span class="line"><span class="keyword">RELEASE</span> <span class="keyword">SAVEPOINT</span> 保存点名称 <span class="comment">-- 删除保存点</span></span><br></pre></td></tr></tbody></table></figure>

<blockquote>
<p>测试</p>
</blockquote>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `account` (</span><br><span class="line">`id` <span class="type">INT</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span> AUTO_INCREMENT,</span><br><span class="line">`name` <span class="type">VARCHAR</span>(<span class="number">32</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">`cash` <span class="type">DECIMAL</span>(<span class="number">9</span>,<span class="number">2</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line"><span class="keyword">PRIMARY</span> KEY (`id`)</span><br><span class="line">) ENGINE<span class="operator">=</span>INNODB <span class="keyword">DEFAULT</span> CHARSET<span class="operator">=</span>utf8</span><br><span class="line"></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> account (`name`,`cash`)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'A'</span>,<span class="number">2000.00</span>),(<span class="string">'B'</span>,<span class="number">10000.00</span>)</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 转账实现</span></span><br><span class="line"><span class="keyword">SET</span> autocommit <span class="operator">=</span> <span class="number">0</span>; <span class="comment">-- 关闭自动提交</span></span><br><span class="line"><span class="keyword">START</span> TRANSACTION;  <span class="comment">-- 开始一个事务,标记事务的起始点</span></span><br><span class="line"><span class="keyword">UPDATE</span> account <span class="keyword">SET</span> cash<span class="operator">=</span>cash<span class="number">-500</span> <span class="keyword">WHERE</span> `name`<span class="operator">=</span><span class="string">'A'</span>;</span><br><span class="line"><span class="keyword">UPDATE</span> account <span class="keyword">SET</span> cash<span class="operator">=</span>cash<span class="operator">+</span><span class="number">500</span> <span class="keyword">WHERE</span> `name`<span class="operator">=</span><span class="string">'B'</span>;</span><br><span class="line"><span class="keyword">COMMIT</span>; <span class="comment">-- 提交事务</span></span><br><span class="line"><span class="keyword">rollback</span>;</span><br><span class="line"><span class="keyword">SET</span> autocommit <span class="operator">=</span> <span class="number">1</span>; <span class="comment">-- 恢复自动提交</span></span><br></pre></td></tr></tbody></table></figure>

<h2 id="7、索引"><a href="#7、索引" class="headerlink" title="7、索引"></a>7、索引</h2><blockquote>
<p>MySQL官方对索引的定义为：<strong>索引（Index）是帮助MySQL高效获取数据的数据结构。</strong></p>
<p>提取句子主干，就可以得到索引的本质：索引是数据结构。</p>
</blockquote>
<h3 id="7-1、索引的分类"><a href="#7-1、索引的分类" class="headerlink" title="7.1、索引的分类"></a>7.1、索引的分类</h3><ul>
<li><p>主键索引（PRIMARY KEY）</p>
<ul>
<li>唯一的标识，主键不可重复</li>
</ul>
</li>
<li><p>唯一索引（UNIQUE KEY）</p>
<ul>
<li>避免出现重复的列出现，唯一索引可以有多个</li>
</ul>
</li>
<li><p>常规索引（KEY/INDEX）</p>
<ul>
<li>默认，index/key关键字设置</li>
</ul>
</li>
<li><p>全文索引（FullText）</p>
<ul>
<li>快速定位数据</li>
</ul>
</li>
</ul>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 索引的使用</span></span><br><span class="line"><span class="comment">-- 1、创建表示给字段添加索引</span></span><br><span class="line"><span class="comment">-- 2、创建完毕后，增加索引</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 显示所有索引信息</span></span><br><span class="line"><span class="keyword">show</span> index <span class="keyword">from</span> student</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 增加一个全文索引，（索引名）列名</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> school.student <span class="keyword">add</span> fulltext index <span class="string">'studentName'</span>(studentName);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- explain分析sql执行情况</span></span><br><span class="line">explain <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student <span class="comment">-- 非全文索引</span></span><br><span class="line"></span><br><span class="line">explain <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student <span class="keyword">where</span> <span class="keyword">match</span>(studentName) against(<span class="string">'刘'</span>)</span><br></pre></td></tr></tbody></table></figure>

<h3 id="7-2、测试索引"><a href="#7-2、测试索引" class="headerlink" title="7.2、测试索引"></a>7.2、测试索引</h3><p>创建表</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `app_user` (</span><br><span class="line">`id` <span class="type">bigint</span>(<span class="number">20</span>) unsigned <span class="keyword">NOT</span> <span class="keyword">NULL</span> AUTO_INCREMENT,</span><br><span class="line">`name` <span class="type">varchar</span>(<span class="number">50</span>) <span class="keyword">DEFAULT</span> <span class="string">''</span> COMMENT <span class="string">'用户昵称'</span>,</span><br><span class="line">`email` <span class="type">varchar</span>(<span class="number">50</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span> COMMENT <span class="string">'用户邮箱'</span>,</span><br><span class="line">`phone` <span class="type">varchar</span>(<span class="number">20</span>) <span class="keyword">DEFAULT</span> <span class="string">''</span> COMMENT <span class="string">'手机号'</span>,</span><br><span class="line">`gender` tinyint(<span class="number">4</span>) unsigned <span class="keyword">DEFAULT</span> <span class="string">'0'</span> COMMENT <span class="string">'性别（0:男；1：女）'</span>,</span><br><span class="line">`password` <span class="type">varchar</span>(<span class="number">100</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span> COMMENT <span class="string">'密码'</span>,</span><br><span class="line">`age` tinyint(<span class="number">4</span>) <span class="keyword">DEFAULT</span> <span class="string">'0'</span> COMMENT <span class="string">'年龄'</span>,</span><br><span class="line">`create_time` datetime <span class="keyword">DEFAULT</span> <span class="built_in">CURRENT_TIMESTAMP</span>,</span><br><span class="line">`update_time` <span class="type">timestamp</span> <span class="keyword">NULL</span> <span class="keyword">DEFAULT</span> <span class="built_in">CURRENT_TIMESTAMP</span> <span class="keyword">ON</span> <span class="keyword">UPDATE</span> <span class="built_in">CURRENT_TIMESTAMP</span>,</span><br><span class="line"><span class="keyword">PRIMARY</span> KEY (`id`)</span><br><span class="line">) ENGINE<span class="operator">=</span>InnoDB <span class="keyword">DEFAULT</span> CHARSET<span class="operator">=</span>utf8mb4 COMMENT<span class="operator">=</span><span class="string">'app用户表'</span></span><br></pre></td></tr></tbody></table></figure>

<p>批量插入数据：100w</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 创建函数</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">FUNCTION</span> IF <span class="keyword">EXISTS</span> mock_data;</span><br><span class="line">DELIMITER $$</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">FUNCTION</span> mock_data()</span><br><span class="line"><span class="keyword">RETURNS</span> <span class="type">INT</span></span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line"><span class="keyword">DECLARE</span> num <span class="type">INT</span> <span class="keyword">DEFAULT</span> <span class="number">1000000</span>;</span><br><span class="line"><span class="keyword">DECLARE</span> i <span class="type">INT</span> <span class="keyword">DEFAULT</span> <span class="number">0</span>;</span><br><span class="line">WHILE i <span class="operator">&lt;</span> num DO</span><br><span class="line">  <span class="keyword">INSERT</span> <span class="keyword">INTO</span> app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)</span><br><span class="line">   <span class="keyword">VALUES</span>(CONCAT(<span class="string">'用户'</span>, i), <span class="string">'24736743@qq.com'</span>, CONCAT(<span class="string">'18'</span>, <span class="built_in">FLOOR</span>(RAND()<span class="operator">*</span>(<span class="number">999999999</span><span class="number">-100000000</span>)<span class="operator">+</span><span class="number">100000000</span>)),<span class="built_in">FLOOR</span>(RAND()<span class="operator">*</span><span class="number">2</span>),UUID(), <span class="built_in">FLOOR</span>(RAND()<span class="operator">*</span><span class="number">100</span>));</span><br><span class="line">  <span class="keyword">SET</span> i <span class="operator">=</span> i <span class="operator">+</span> <span class="number">1</span>;</span><br><span class="line"><span class="keyword">END</span> WHILE;</span><br><span class="line"><span class="keyword">RETURN</span> i;</span><br><span class="line"><span class="keyword">END</span>;</span><br><span class="line"><span class="keyword">SELECT</span> mock_data();  <span class="comment">-- 执行函数</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 报错1418</span></span><br><span class="line"><span class="keyword">set</span> <span class="keyword">global</span> log_bin_trust_function_creators<span class="operator">=</span><span class="literal">TRUE</span>;</span><br></pre></td></tr></tbody></table></figure>

<p>索引效率</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 无索引</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> app_user <span class="keyword">WHERE</span> name <span class="operator">=</span> <span class="string">'用户9999'</span>; <span class="comment">-- 查看耗时 0.875s</span></span><br><span class="line">explain <span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> app_user <span class="keyword">WHERE</span> name <span class="operator">=</span> <span class="string">'用户9999'</span>;   <span class="comment">-- 查询了99万多条数据</span></span><br></pre></td></tr></tbody></table></figure>

<p><img src="https://yichen-blog.oss-cn-beijing.aliyuncs.com//note-image/2023/07/16/20230716-162049.png" alt="image-20210527095554275"></p>
<p>创建索引</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> INDEX idx_app_user_name <span class="keyword">ON</span> app_user(name); <span class="comment">-- 耗时13s</span></span><br></pre></td></tr></tbody></table></figure>

<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 有索引</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> app_user <span class="keyword">WHERE</span> name <span class="operator">=</span> <span class="string">'用户9999'</span>; <span class="comment">-- 查看耗时 0.015s</span></span><br><span class="line">explain <span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> app_user <span class="keyword">WHERE</span> name <span class="operator">=</span> <span class="string">'用户9999'</span>;   <span class="comment">-- 查询了1条数据</span></span><br></pre></td></tr></tbody></table></figure>

<p><img src="https://yichen-blog.oss-cn-beijing.aliyuncs.com//note-image/2023/07/16/20230716-162051.png" alt="image-20210527095945670"></p>
<h3 id="7-3、索引原则"><a href="#7-3、索引原则" class="headerlink" title="7.3、索引原则"></a>7.3、索引原则</h3><ul>
<li>索引不是越多越好</li>
<li>不要对经常变动的数据加索引</li>
<li>小数据量的表建议不要加索引</li>
<li>索引一般应加在查找条件的字段</li>
</ul>

      
    </div>
    <div class="article-footer">
      <blockquote class="mt-2x">
  <ul class="post-copyright list-unstyled">
    
    <li class="post-copyright-link hidden-xs">
      <strong>本文链接：</strong>
      <a href="https://yichenfirst.github.io/2021/08/31/mysql/mysql/" title="mysql基础" target="_blank" rel="external">https://yichenfirst.github.io/2021/08/31/mysql/mysql/</a>
    </li>
    
    <li class="post-copyright-license">
      <strong>版权声明： </strong> 本博客所有文章除特别声明外，均采用 <a href="http://creativecommons.org/licenses/by/4.0/deed.zh" target="_blank" rel="external">CC BY 4.0 CN协议</a> 许可协议。转载请注明出处！
    </li>
  </ul>
</blockquote>


<div class="panel panel-default panel-badger">
  <div class="panel-body">
    <figure class="media">
      <div class="media-left">
        <a href="" target="_blank" class="img-burn thumb-sm visible-lg">
          <img src="/images/avatar.jpg" class="img-rounded w-full" alt="">
        </a>
      </div>
      <div class="media-body">
        <h3 class="media-heading"><a href="" target="_blank"><span class="text-dark">逸辰</span><small class="ml-1x">后端开发</small></a></h3>
        <div>个人简介。</div>
      </div>
    </figure>
  </div>
</div>


    </div>
  </article>
  
    
  <section id="comments">
  	
      <div id="vcomments"></div>
    
  </section>


  
</div>

  <nav class="bar bar-footer clearfix" data-stick-bottom>
  <div class="bar-inner">
  
  <ul class="pager pull-left">
    
    <li class="prev">
      <a href="/2021/08/31/%E5%89%8D%E7%AB%AF/vue%E5%AE%89%E8%A3%85%E6%95%99%E7%A8%8B/" title="Vue安装教程"><i class="icon icon-angle-left" aria-hidden="true"></i><span>&nbsp;&nbsp;上一篇</span></a>
    </li>
    
    
    <li class="next">
      <a href="/2021/08/01/%E5%89%8D%E7%AB%AF/vue%E8%84%9A%E6%89%8B%E6%9E%B6%E4%B8%8Evuex/" title="vue脚手架与vuex"><span>下一篇&nbsp;&nbsp;</span><i class="icon icon-angle-right" aria-hidden="true"></i></a>
    </li>
    
    
  </ul>
  
  
  <!-- Button trigger modal -->
  <button type="button" class="btn btn-fancy btn-donate pop-onhover bg-gradient-warning" data-toggle="modal" data-target="#donateModal"><span>赏</span></button>
  <!-- <div class="wave-icon wave-icon-danger btn-donate" data-toggle="modal" data-target="#donateModal">
    <div class="wave-circle"><span class="icon"><i class="icon icon-bill"></i></span></div>
  </div> -->
  
  
  <div class="bar-right">
    
  </div>
  </div>
</nav>
  
<!-- Modal -->
<div class="modal modal-center modal-small modal-xs-full fade" id="donateModal" tabindex="-1" role="dialog">
  <div class="modal-dialog" role="document">
    <div class="modal-content donate">
      <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
      <div class="modal-body">
        <div class="donate-box">
          <div class="donate-head">
            <p>感谢您的支持，我会继续努力的!</p>
          </div>
          <div class="tab-content">
            <div role="tabpanel" class="tab-pane fade active in" id="alipay">
              <div class="donate-payimg">
                <img src="/images/donate/alipayimg.png" alt="扫码支持" title="扫一扫" />
              </div>
              <p class="text-muted mv">扫码打赏，你说多少就多少</p>
              <p class="text-grey">打开支付宝扫一扫，即可进行扫码打赏哦</p>
            </div>
            <div role="tabpanel" class="tab-pane fade" id="wechatpay">
              <div class="donate-payimg">
                <img src="/images/donate/wechatpayimg.png" alt="扫码支持" title="扫一扫" />
              </div>
              <p class="text-muted mv">扫码打赏，你说多少就多少</p>
              <p class="text-grey">打开微信扫一扫，即可进行扫码打赏哦</p>
            </div>
          </div>
          <div class="donate-footer">
            <ul class="nav nav-tabs nav-justified" role="tablist">
              <li role="presentation" class="active">
                <a href="#alipay" id="alipay-tab" role="tab" data-toggle="tab" aria-controls="alipay" aria-expanded="true"><i class="icon icon-alipay"></i> 支付宝</a>
              </li>
              <li role="presentation" class="">
                <a href="#wechatpay" role="tab" id="wechatpay-tab" data-toggle="tab" aria-controls="wechatpay" aria-expanded="false"><i class="icon icon-wepay"></i> 微信支付</a>
              </li>
            </ul>
          </div>
        </div>
      </div>
    </div>
  </div>
</div>



</main>

  <footer class="footer" itemscope itemtype="http://schema.org/WPFooter">
	
    <div class="copyright">
    	
        &copy; 2023 逸辰
        
        <div class="publishby">
        	Theme by <a href="https://github.com/cofess" target="_blank"> cofess </a>base on <a href="https://github.com/cofess/hexo-theme-pure" target="_blank">pure</a>.
        </div>
    </div>
</footer>
  <script src="//cdn.jsdelivr.net/npm/jquery@1.12.4/dist/jquery.min.js"></script>
<script>
window.jQuery || document.write('<script src="js/jquery.min.js"><\/script>')
</script>

<script src="/js/plugin.min.js"></script>


<script src="/js/application.js"></script>


    <script>
(function (window) {
    var INSIGHT_CONFIG = {
        TRANSLATION: {
            POSTS: '文章',
            PAGES: '页面',
            CATEGORIES: '分类',
            TAGS: '标签',
            UNTITLED: '(未命名)',
        },
        ROOT_URL: '/',
        CONTENT_URL: '/content.json',
    };
    window.INSIGHT_CONFIG = INSIGHT_CONFIG;
})(window);
</script>

<script src="/js/insight.js"></script>






   
<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>





   
    
  <script src="//cdn1.lncld.net/static/js/3.0.4/av-min.js"></script>
  <script src="//cdn.jsdelivr.net/npm/valine"></script>
  <script type="text/javascript">
  var GUEST = ['nick', 'mail', 'link'];
  var meta = 'nick,mail,link';
  meta = meta.split(',').filter(function(item) {
    return GUEST.indexOf(item) > -1;
  });
  new Valine({
    el: '#vcomments',
    verify: false,
    notify: false,
    appId: '',
    appKey: '',
    placeholder: 'Just go go',
    avatar: 'mm',
    meta: meta,
    pageSize: '10' || 10,
    visitor: false
  });
  </script>

     







</body>
</html>